SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

/*****************************************************************************/
/*                           Liberty                                         */
/*****************************************************************************/
/* Stored Procedure :: SavePerson()                                          */
/*                                                                           */
/*****************************************************************************/
/* Copyright (C) 2003, Perot Systems Corporation. All right reserved.        */
/* Developer: Ed. Mueller (edward.mueller@ps.net)                            */
/* Started:  10/14/2003                                                      */
/*****************************************************************************/
/* Modifications                                                             */
/* Date       |  Author          | Description                               */
/*------------+------------------+-------------------------------------------*/
/*            |                  |                                           */
/*****************************************************************************/

/******************************************************************************/
/* Step 0. Preparations/Declarations                                          */
/******************************************************************************/
SET NOCOUNT OFF

/******************************************************************************/
/* Step 1. Remove stored procedure if it already exists                       */
/******************************************************************************/
IF (SELECT OBJECT_ID('[Kernel].[SavePerson]')) IS NOT NULL BEGIN
  DROP PROCEDURE [Kernel].[SavePerson] END

GO

/*****************************************************************************/
/* Stored Procedure :: SavePerson                                            */
/*****************************************************************************/
/* Copyright (C) 2003, Perot Systems Corporation. All right reserved.        */
/* Developer: Ed. Mueller (edward.mueller@ps.net)                            */
/* Started:  10/09/2003                                                      */
/*****************************************************************************/
CREATE PROCEDURE [Kernel].[SavePerson]
@OID			bigint			= NULL OUTPUT,
@Version        rowversion		= NULL OUTPUT,
@FirstName		varchar(128)	= NULL,
@LastName		varchar(128)	= NULL,
@MiddleInitial	char(01)		= NULL,
@DOB			smalldatetime	= NULL,
@Sex			char(01)		= NULL,
@Phone1			varchar(20)		= NULL,
@Phone2			varchar(20)		= NULL,
@eMailAddress	varchar(128)	= NULL

AS 

---------------------------------------------------------------------------------------------------
--						Liberty
---------------------------------------------------------------------------------------------------
--  Stored Procedure :: SavePerson
--                                                                            
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON 

DECLARE @RetCode int,
        @RowCount int

-- 1. If new record (OID is not set)
IF ISNULL(@OID, 0) <= 0 
BEGIN
	-- 1.1. Perform insert (OID is identity)
	INSERT INTO dbo.People
	(FirstName, LastName, MiddleInitial, DOB, Sex, Phone1, Phone2, 
	eMailAddress, UpdatedDate, CreatedDate)
	VALUES
	(@FirstName, @LastName, @MiddleInitial, @DOB, @Sex, @Phone1, @Phone2, 
	@eMailAddress, GETDATE(), GETDATE())

	-- 1.2. Determine OID of newly inserted object
	SET @OID = SCOPE_IDENTITY()
END

-- 2. If OID is set (existing record)
ELSE
BEGIN
    IF (@Version IS NULL)
    BEGIN
    
	    -- 2.1. Attempt updating object using just OID
	    UPDATE dbo.People
	    SET FirstName = @FirstName
		    ,LastName = @LastName
		    ,MiddleInitial = @MiddleInitial
		    ,DOB = @DOB
		    ,Sex = @Sex
		    ,Phone1 = @Phone1
		    ,Phone2 = @Phone2
		    ,eMailAddress = @eMailAddress
		    ,UpdatedDate = GETDATE()
	    WHERE (OID = @OID) 
        SELECT @RetCode = @@ERROR, @RowCount = @@ROWCOUNT
        IF (@RetCode <> 0) GOTO ExitError
	    -- 2.2. If no records were found (and updated) - it means that
	    -- the record was deleted (OID not found)
	    -- Either way, we need to raise exception
	    IF @RowCount = 0
	    BEGIN
		    RAISERROR(99999, 16, 1, 'Person' ) --WITH LOG
		    GOTO ExitError
	    END
	END
	ELSE
	BEGIN
    
	    -- 2.3. Attempt updating object (using both OID and timestamp for concurrency reasons)
	    UPDATE dbo.People
	    SET FirstName = @FirstName
		    ,LastName = @LastName
		    ,MiddleInitial = @MiddleInitial
		    ,DOB = @DOB
		    ,Sex = @Sex
		    ,Phone1 = @Phone1
		    ,Phone2 = @Phone2
		    ,eMailAddress = @eMailAddress
		    ,UpdatedDate = GETDATE()
	    WHERE ((OID = @OID) AND (Version = @Version))
        SELECT @RetCode = @@ERROR, @RowCount = @@ROWCOUNT
        IF (@RetCode <> 0) GOTO ExitError

	    -- 2.4. If no records were found (and updated) - it means that
	    -- the record was deleted (OID not found)
	    -- Either way, we need to raise exception
	    IF @RowCount = 0
	    BEGIN
		    RAISERROR(99999, 16, 1, 'Person' ) --WITH LOG
		    GOTO ExitError
	    END
	END
END

-- 3. Return the version.
SELECT @Version = Version FROM dbo.People
WHERE (OID = @OID)

RETURN(0)

ExitError:
	RETURN(1)
GO

/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [Kernel].[SavePerson] TO [Liberty_Users] 
GO
-- Developers and Admins have permissions to execute through membership   